import json
import openpyxl
from openpyxl.styles import PatternFill

def readExcel(file, sheets=[]):
    workbook = openpyxl.load_workbook(file)
    result = {}
    repeat = {}
    has_repeat = False
    for sheet in workbook.worksheets:
        if 0 != len(sheets) and sheet.title not in sheets: continue
        fields = {}
        tmp_fields = []
        repeat[sheet.title] = []
        for col in range(1, sheet.max_column + 1):
            value = str(sheet.cell(1, col).value)
            if value in tmp_fields:
                has_repeat = True
                repeat[sheet.title].append(value)
            fields[col] = value
            tmp_fields.append(value)
        result[sheet.title] = {}
        for row in range(2, sheet.max_row + 1):
            result[sheet.title][str(row)] = {}
            for col in range(1, sheet.max_column + 1):
                result[sheet.title][str(row)][fields[col]] = {}
                result[sheet.title][str(row)][fields[col]]["v"] = str(sheet.cell(row, col).value)
                result[sheet.title][str(row)][fields[col]]["p"] = {"x": row, "y": col}
    if has_repeat:
        raise Exception(f"有重复字段: {repeat}")
    return result
def parseScopt(scope):
    scope_str = f"[{scope}]"
    try: scope_arr = json.loads(scope_str)
    except: raise Exception("scope: 不正确的格式")
    result = []
    for i in scope_arr:
        if type(i) is list:
            for j in range(i[0], i[1] + 1):
                result.append(j)
        else:
            result.append(i)
    return result
def addFields(params, from_data, fields, fill):
    workbook = openpyxl.load_workbook(params["to"]["file"])
    for sheet in params["sheets"]:
        ws = workbook[sheet]
        i_len = fields[sheet]["len"]
        for x in range(1, len(fields[sheet]["field"]) + 1):
            field = fields[sheet]["field"][x - 1]
            j_len = i_len + x
            ws.cell(row = 1, column = j_len).value = field
            ws.cell(row = 1, column = j_len).fill = fill
            ws.cell(row = 2, column = j_len).value = from_data[sheet]["2"][field]["v"]
            ws.cell(row = 2, column = j_len).fill = fill
    workbook.save(params["to"]["file"])
def addRows(params, rows, fill):
    workbook = openpyxl.load_workbook(params["to"]["file"])
    for sheet in params["sheets"]:
        ws = workbook[sheet]
        for y in range(len(rows[sheet]["row"])):
            ws.cell(row = rows[sheet]["len"] + y, column = 1).value = "new_row"
            ws.cell(row = rows[sheet]["len"] + y, column = 1).fill = fill
    workbook.save(params["to"]["file"])
def main(params):
    from_scope = parseScopt(params["from"]["scope"])
    to_scope = parseScopt(params["to"]["scope"])
    if 1 in to_scope or 2 in to_scope or 1 in from_scope or 2 in from_scope:
        raise Exception("scope 不能包含 1 和 2")
    if len(from_scope) != len(to_scope): raise Exception("from 和 to 的 scope 长度不相等")
    from_data = readExcel(params["from"]["file"], params["sheets"])
    to_data = readExcel(params["to"]["file"], params["sheets"])
    result = []
    new_fields = {}
    new_rows = {}
    for sheet in params["sheets"]:
        new_fields[sheet] = {}
        new_fields[sheet]["field"] = []
        new_fields[sheet]["len"] = len(to_data[sheet]["2"])
        for field in from_data[sheet]["2"]:
            if field not in to_data[sheet]["2"]:
                result.append(f'{sheet}: "from" 新增字段: {field}')
                new_fields[sheet]["field"].append(field)
            elif to_data[sheet]["2"][field]["v"] != from_data[sheet]["2"][field]["v"]:
                result.append(f'{sheet}: 第2行不一样的字段: "to": {field}="{to_data[sheet]["2"][field]["v"]}", "from": {field}="{from_data[sheet]["2"][field]["v"]}"')
        new_rows[sheet] = {}
        new_rows[sheet]["row"] = []
        new_rows[sheet]["len"] = len(to_data[sheet]) + 2  # 要算上第1、2行占的位置
        for row in to_scope:
            if str(row) not in to_data[sheet]:
                new_rows[sheet]["row"].append(row)
    if 0 == len(result): print('"from" 无新增字段, 且第2行内容无变化')
    else: print("\n".join(result))
    params["color"] = "ffffff" if "" == params["color"] else params["color"]
    fill = PatternFill("solid", fgColor=params["color"])
    addFields(params, from_data, new_fields, fill)
    addRows(params, new_rows, fill)
    to_data = readExcel(params["to"]["file"], params["sheets"])  # 新增字段、新增行后重新读取目标文件
    merge(params, from_data, to_data, from_scope, to_scope, fill)
def merge(params, from_data, to_data, from_scope, to_scope, fill):
    result = []
    workbook = openpyxl.load_workbook(params["to"]["file"])
    for sheet in params["sheets"]:
        ws = workbook[sheet]
        for scope in range(len(from_scope)):
            for field in from_data[sheet][str(from_scope[scope])]:
                if field not in to_data[sheet][str(to_scope[scope])]: continue
                from_value = from_data[sheet][str(from_scope[scope])][field]["v"]
                to_value = to_data[sheet][str(to_scope[scope])][field]["v"]
                if from_value != to_value:
                    p = to_data[sheet][str(to_scope[scope])][field]["p"]
                    result.append(f'{sheet}: "to" 第 {p["x"]} 行, {field} 从 "{to_value}" 变成了 "{from_value}"')
                    if "None" == from_value: from_value = None
                    ws.cell(row = p["x"], column = p["y"]).value = from_value
                    ws.cell(row = p["x"], column = p["y"]).fill = fill
    workbook.save(params["to"]["file"])
    print("\n".join(result))

if __name__ == '__main__':
    params = {
        "from": {
            "file": "from.xlsx",
            "scope": "[3,12]"
        },
        "to": {
            "file": "to.xlsx",
            "scope": "[4,13]"
        },
        "sheets": ["Sheet1"],
        "color": "ffff00"
    }
    main(params)
